# -*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
from utils.dbbase import exc_sql

def user_apiflage(cop_id):
    sql = "SELECT autapiflag,apiurl,name FROM app_key WHERE  cop_id = '%s'" % (cop_id)
    data = exc_sql (sql, fetch_type=2)
    return data


def user_password(uid, passwd, cop_id):
    sql = "SELECT * FROM qrcode_user WHERE userid = '%s' AND cop_id = '%s'"%(uid,cop_id)
    data = exc_sql(sql,fetch_type=2)
    if data:
        if passwd == data[2]:
            return True
        return False
    return False



def user_info(uid,cop_id):
    sql = "SELECT * FROM qrcode_user WHERE userid = '%s' AND cop_id = '%s'AND is_del=FALSE " % (uid, cop_id)
    data = exc_sql(sql, fetch_type=2)
    return data

def bind_openid(**param):

    open_id = None if param['open_id'] == '' else param['open_id']

    sql="UPDATE qrcode_user SET is_del=TRUE WHERE open_id='{}'".format(open_id) #将注册状态 改为未注册
    exc_sql (sql, fetch_type=3)
    #存在则更新，不存在则插入
    sql="INSERT INTO qrcode_user(userid,password,cop_id,open_id,cop_name,name,is_del,department,class_,major) " \
        "VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}') " \
        "ON conflict(userid,cop_id) " \
        "DO UPDATE SET  open_id='{3}',is_del=FALSE "\
        .format (param['uid'],param['passwd'],param['cop_id'],param['open_id'],param['cop_name'],param['name'],False,param['department'],param['class_'],param['major'])
    exc_sql (sql, fetch_type=3)




# def user_info_by_openid(openid,cop_id):
#     sql = "SELECT uid FROM qrcode_user WHERE open_id = '%s' AND cop_id = '%s'" % (openid, cop_id)
#     data = exc_sql(sql, fetch_type=2)
#     return data

def user_info_by_openid(openid):
    openid = None if openid == '' else openid
    sql = "SELECT userid FROM qrcode_user WHERE open_id = '{}'AND is_del=FALSE ".format(openid)
    data = exc_sql(sql, fetch_type=2)
    return data


def u_info_by_openid(openid):
    openid = None if openid == '' else openid
    sql = "SELECT userid,cop_name,name,cop_id  FROM qrcode_user WHERE open_id = '{}'AND is_del=FALSE".format(openid)
    print sql
    data = exc_sql(sql, fetch_type=2)
    print data
    return data

def select_schoollist(openid):
    sql = "SELECT app_key.name,app_key.cop_id,app_key.iconame FROM app_key"
    data = exc_sql (sql, fetch_type=1) #查询多条
    print data
    return data

def userunbind_tool(uid, cop_id, open_id):

    sql="UPDATE qrcode_user SET is_del=TRUE WHERE userid='{}'AND cop_id='{}'".format(uid,cop_id)
    data = exc_sql(sql, fetch_type=3)  # 执行 sql
    print data
    return data








